/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     04/04/2014 2:57:27 PM                        */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ACCOUNTS') and o.name = 'FK_ACCOUNTS_ACCOUNTTY_ACCOUNT_')
alter table ACCOUNTS
   drop constraint FK_ACCOUNTS_ACCOUNTTY_ACCOUNT_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ACCOUNTS') and o.name = 'FK_ACCOUNTS_BANKS_ACC_BANKS')
alter table ACCOUNTS
   drop constraint FK_ACCOUNTS_BANKS_ACC_BANKS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ACCOUNTS') and o.name = 'FK_ACCOUNTS_CITIES_AC_CITIES')
alter table ACCOUNTS
   drop constraint FK_ACCOUNTS_CITIES_AC_CITIES
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ACCOUNTS') and o.name = 'FK_ACCOUNTS_COUNTRIES_COUNTRIE')
alter table ACCOUNTS
   drop constraint FK_ACCOUNTS_COUNTRIES_COUNTRIE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ACCOUNTS') and o.name = 'FK_ACCOUNTS_RELATIONS_USERS')
alter table ACCOUNTS
   drop constraint FK_ACCOUNTS_RELATIONS_USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CITIES') and o.name = 'FK_CITIES_COUNTRIES_COUNTRIE')
alter table CITIES
   drop constraint FK_CITIES_COUNTRIES_COUNTRIE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DEPARTMENTS_TICKETCATEGORIES') and o.name = 'FK_DEPARTME_DEPARTMEN_DEPARTME')
alter table DEPARTMENTS_TICKETCATEGORIES
   drop constraint FK_DEPARTME_DEPARTMEN_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DEPARTMENTS_TICKETCATEGORIES') and o.name = 'FK_DEPARTME_TICKETCAT_TICKET_C')
alter table DEPARTMENTS_TICKETCATEGORIES
   drop constraint FK_DEPARTME_TICKETCAT_TICKET_C
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DOWNLOADS') and o.name = 'FK_DOWNLOAD_PROGRAMMI_PROGRAMM')
alter table DOWNLOADS
   drop constraint FK_DOWNLOAD_PROGRAMMI_PROGRAMM
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ORDERS') and o.name = 'FK_ORDERS_ACCOUNTS__ACCOUNTS')
alter table ORDERS
   drop constraint FK_ORDERS_ACCOUNTS__ACCOUNTS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ORDERS') and o.name = 'FK_ORDERS_ORDERSTAT_ORDER_ST')
alter table ORDERS
   drop constraint FK_ORDERS_ORDERSTAT_ORDER_ST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ORDERS') and o.name = 'FK_ORDERS_STAFFS_OR_STAFFS')
alter table ORDERS
   drop constraint FK_ORDERS_STAFFS_OR_STAFFS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ORDER_LINES') and o.name = 'FK_ORDER_LI_ORDER_ORD_ORDERS')
alter table ORDER_LINES
   drop constraint FK_ORDER_LI_ORDER_ORD_ORDERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('ORDER_LINES') and o.name = 'FK_ORDER_LI_SERVICES__SERVICES')
alter table ORDER_LINES
   drop constraint FK_ORDER_LI_SERVICES__SERVICES
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SERVICES') and o.name = 'FK_SERVICES_SERVICETY_SERVICE_')
alter table SERVICES
   drop constraint FK_SERVICES_SERVICETY_SERVICE_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SERVICETYPES_TICKETCATEGORIES') and o.name = 'FK_SERVICET_SERVICETY_SERVICE_')
alter table SERVICETYPES_TICKETCATEGORIES
   drop constraint FK_SERVICET_SERVICETY_SERVICE_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('SERVICETYPES_TICKETCATEGORIES') and o.name = 'FK_SERVICET_TICKETCAT_TICKET_C')
alter table SERVICETYPES_TICKETCATEGORIES
   drop constraint FK_SERVICET_TICKETCAT_TICKET_C
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('STAFFS') and o.name = 'FK_STAFFS_USERS_STA_USERS')
alter table STAFFS
   drop constraint FK_STAFFS_USERS_STA_USERS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKETS') and o.name = 'FK_TICKETS_DEPARTMEN_DEPARTME')
alter table TICKETS
   drop constraint FK_TICKETS_DEPARTMEN_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKETS') and o.name = 'FK_TICKETS_ORDERLINE_ORDER_LI')
alter table TICKETS
   drop constraint FK_TICKETS_ORDERLINE_ORDER_LI
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKETS') and o.name = 'FK_TICKETS_SERVICETY_SERVICE_')
alter table TICKETS
   drop constraint FK_TICKETS_SERVICETY_SERVICE_
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKETS') and o.name = 'FK_TICKETS_STAFFS_TI_STAFFS')
alter table TICKETS
   drop constraint FK_TICKETS_STAFFS_TI_STAFFS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKETS') and o.name = 'FK_TICKETS_TICKETCAT_TICKET_C')
alter table TICKETS
   drop constraint FK_TICKETS_TICKETCAT_TICKET_C
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKETS') and o.name = 'FK_TICKETS_TICKETSTA_TICKET_S')
alter table TICKETS
   drop constraint FK_TICKETS_TICKETSTA_TICKET_S
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('TICKET_COMMENTS') and o.name = 'FK_TICKET_C_TICKETS_T_TICKETS')
alter table TICKET_COMMENTS
   drop constraint FK_TICKET_C_TICKETS_T_TICKETS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('USERS') and o.name = 'FK_USERS_USERTYPES_USER_TYP')
alter table USERS
   drop constraint FK_USERS_USERTYPES_USER_TYP
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ACCOUNTS')
            and   name  = 'BANKS_ACCOUNTS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ACCOUNTS.BANKS_ACCOUNTS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ACCOUNTS')
            and   name  = 'COUNTRIES_ACCOUNTS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ACCOUNTS.COUNTRIES_ACCOUNTS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ACCOUNTS')
            and   name  = 'CITIES_ACCOUNTS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ACCOUNTS.CITIES_ACCOUNTS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ACCOUNTS')
            and   name  = 'ACCOUNTTYPES_ACCOUNTS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ACCOUNTS.ACCOUNTTYPES_ACCOUNTS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ACCOUNTS')
            and   name  = 'RELATIONSHIP_2_FK'
            and   indid > 0
            and   indid < 255)
   drop index ACCOUNTS.RELATIONSHIP_2_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ACCOUNTS')
            and   type = 'U')
   drop table ACCOUNTS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ACCOUNT_TYPES')
            and   type = 'U')
   drop table ACCOUNT_TYPES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BANKS')
            and   type = 'U')
   drop table BANKS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CITIES')
            and   name  = 'COUNTRIES_CITIES_FK'
            and   indid > 0
            and   indid < 255)
   drop index CITIES.COUNTRIES_CITIES_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CITIES')
            and   type = 'U')
   drop table CITIES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('COUNTRIES')
            and   type = 'U')
   drop table COUNTRIES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DEPARTMENTS')
            and   type = 'U')
   drop table DEPARTMENTS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DEPARTMENTS_TICKETCATEGORIES')
            and   name  = 'DEPARTMENTS_TICKETCATEGORIES_FK'
            and   indid > 0
            and   indid < 255)
   drop index DEPARTMENTS_TICKETCATEGORIES.DEPARTMENTS_TICKETCATEGORIES_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DEPARTMENTS_TICKETCATEGORIES')
            and   name  = 'TICKETCATEGORIES_DEPARTMENTS_FK'
            and   indid > 0
            and   indid < 255)
   drop index DEPARTMENTS_TICKETCATEGORIES.TICKETCATEGORIES_DEPARTMENTS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DEPARTMENTS_TICKETCATEGORIES')
            and   type = 'U')
   drop table DEPARTMENTS_TICKETCATEGORIES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('DOWNLOADS')
            and   name  = 'PROGRAMMINGLANGAUGES_DOWNLOADS_FK'
            and   indid > 0
            and   indid < 255)
   drop index DOWNLOADS.PROGRAMMINGLANGAUGES_DOWNLOADS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DOWNLOADS')
            and   type = 'U')
   drop table DOWNLOADS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ORDERS')
            and   name  = 'STAFFS_ORDERS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ORDERS.STAFFS_ORDERS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ORDERS')
            and   name  = 'ACCOUNTS_ORDERS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ORDERS.ACCOUNTS_ORDERS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ORDERS')
            and   name  = 'ORDERSTATUSES_ORDERS_FK'
            and   indid > 0
            and   indid < 255)
   drop index ORDERS.ORDERSTATUSES_ORDERS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ORDERS')
            and   type = 'U')
   drop table ORDERS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ORDER_LINES')
            and   name  = 'SERVICES_ORDERLINES_FK'
            and   indid > 0
            and   indid < 255)
   drop index ORDER_LINES.SERVICES_ORDERLINES_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('ORDER_LINES')
            and   name  = 'ORDER_ORDERLINES_FK'
            and   indid > 0
            and   indid < 255)
   drop index ORDER_LINES.ORDER_ORDERLINES_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ORDER_LINES')
            and   type = 'U')
   drop table ORDER_LINES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ORDER_STATUSES')
            and   type = 'U')
   drop table ORDER_STATUSES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PROGRAMMING_LANGUAGES')
            and   type = 'U')
   drop table PROGRAMMING_LANGUAGES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('SERVICES')
            and   name  = 'SERVICETYPES_SERVICES_FK'
            and   indid > 0
            and   indid < 255)
   drop index SERVICES.SERVICETYPES_SERVICES_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SERVICES')
            and   type = 'U')
   drop table SERVICES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('SERVICETYPES_TICKETCATEGORIES')
            and   name  = 'TICKETCATEGORIES_SERVICETYPES_FK'
            and   indid > 0
            and   indid < 255)
   drop index SERVICETYPES_TICKETCATEGORIES.TICKETCATEGORIES_SERVICETYPES_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('SERVICETYPES_TICKETCATEGORIES')
            and   name  = 'SERVICETYPES_TICKETCATEGORIES_FK'
            and   indid > 0
            and   indid < 255)
   drop index SERVICETYPES_TICKETCATEGORIES.SERVICETYPES_TICKETCATEGORIES_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SERVICETYPES_TICKETCATEGORIES')
            and   type = 'U')
   drop table SERVICETYPES_TICKETCATEGORIES
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SERVICE_TYPES')
            and   type = 'U')
   drop table SERVICE_TYPES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('STAFFS')
            and   name  = 'USERS_STAFFS_FK'
            and   indid > 0
            and   indid < 255)
   drop index STAFFS.USERS_STAFFS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('STAFFS')
            and   type = 'U')
   drop table STAFFS
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKETS')
            and   name  = 'ORDERLINES_TICKETS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKETS.ORDERLINES_TICKETS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKETS')
            and   name  = 'TICKETSTATUSES_TICKETS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKETS.TICKETSTATUSES_TICKETS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKETS')
            and   name  = 'STAFFS_TICKETS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKETS.STAFFS_TICKETS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKETS')
            and   name  = 'SERVICETYPES_TICKETS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKETS.SERVICETYPES_TICKETS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKETS')
            and   name  = 'DEPARTMENTS_TICKETS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKETS.DEPARTMENTS_TICKETS_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKETS')
            and   name  = 'TICKETCATEGORIES_TICKETS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKETS.TICKETCATEGORIES_TICKETS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TICKETS')
            and   type = 'U')
   drop table TICKETS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TICKET_CATEGORIES')
            and   type = 'U')
   drop table TICKET_CATEGORIES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('TICKET_COMMENTS')
            and   name  = 'TICKETS_TICKETCOMMENTS_FK'
            and   indid > 0
            and   indid < 255)
   drop index TICKET_COMMENTS.TICKETS_TICKETCOMMENTS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TICKET_COMMENTS')
            and   type = 'U')
   drop table TICKET_COMMENTS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('TICKET_STATUSES')
            and   type = 'U')
   drop table TICKET_STATUSES
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('USERS')
            and   name  = 'USERTYPES_USERS_FK'
            and   indid > 0
            and   indid < 255)
   drop index USERS.USERTYPES_USERS_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('USERS')
            and   type = 'U')
   drop table USERS
go

if exists (select 1
            from  sysobjects
           where  id = object_id('USER_TYPES')
            and   type = 'U')
   drop table USER_TYPES
go

/*==============================================================*/
/* Table: ACCOUNTS                                              */
/*==============================================================*/
create table ACCOUNTS (
   ACC_ID               numeric(11)          identity,
   CNT_ID               numeric(11)          not null,
   BNK_ID               numeric(11)          not null,
   USR_ID               numeric(11)          not null,
   ACT_ID               numeric(11)          not null,
   CTY_ID               numeric(11)          not null,
   ACC_CODE             nvarchar(9)           not null,
   ACC_OWNER_NAME       nvarchar(50)          not null,
   ACC_DELEGATE_NAME    nvarchar(50)          null,
   ACC_ADDRESS          nvarchar(200)         not null,
   ACC_ALT_EMAIL        nvarchar(100)         null,
   ACC_CELL_PHONE       nvarchar(15)          not null,
   ACC_HOME_PHONE       nvarchar(15)          null,
   ACC_FAX_NUMBER       nvarchar(15)          null,
   ACC_TAX_CODE         nvarchar(15)          null,
   ACC_ID_NUMBER        nvarchar(10)          not null,
   ACC_BIRTHDAY         datetime             not null,
   ACC_HOME_PAGE        nvarchar(100)         null,
   ACC_BANK_ACCOUNT_NUMBER nvarchar(15)          null,
   ACC_ID_FRONT_SIDE    nvarchar(100)         null,
   ACC_ID_BACK_SIDE     nvarchar(100)         null,
   constraint PK_ACCOUNTS primary key nonclustered (ACC_ID),
   constraint AK_ACC_CODE_ACCOUNTS unique (ACC_CODE),
   constraint AK_ACC_ID_NUMBER_ACCOUNTS unique (ACC_ID_NUMBER)
)
go

/*==============================================================*/
/* Index: RELATIONSHIP_2_FK                                     */
/*==============================================================*/
create index RELATIONSHIP_2_FK on ACCOUNTS (
USR_ID ASC
)
go

/*==============================================================*/
/* Index: ACCOUNTTYPES_ACCOUNTS_FK                              */
/*==============================================================*/
create index ACCOUNTTYPES_ACCOUNTS_FK on ACCOUNTS (
ACT_ID ASC
)
go

/*==============================================================*/
/* Index: CITIES_ACCOUNTS_FK                                    */
/*==============================================================*/
create index CITIES_ACCOUNTS_FK on ACCOUNTS (
CTY_ID ASC
)
go

/*==============================================================*/
/* Index: COUNTRIES_ACCOUNTS_FK                                 */
/*==============================================================*/
create index COUNTRIES_ACCOUNTS_FK on ACCOUNTS (
CNT_ID ASC
)
go

/*==============================================================*/
/* Index: BANKS_ACCOUNTS_FK                                     */
/*==============================================================*/
create index BANKS_ACCOUNTS_FK on ACCOUNTS (
BNK_ID ASC
)
go

/*==============================================================*/
/* Table: ACCOUNT_TYPES                                         */
/*==============================================================*/
create table ACCOUNT_TYPES (
   ACT_ID               numeric(11)          identity,
   ACT_NAME             nvarchar(100)         not null,
   ACT_DESC             nvarchar(500)         null,
   constraint PK_ACCOUNT_TYPES primary key nonclustered (ACT_ID),
   constraint AK_ACT_NAME_ACCOUNT_ unique (ACT_NAME)
)
go

/*==============================================================*/
/* Table: BANKS                                                 */
/*==============================================================*/
create table BANKS (
   BNK_ID               numeric(11)          identity,
   BNK_NAME             nvarchar(200)         not null,
   BNK_DESC             nvarchar(500)         null,
   constraint PK_BANKS primary key nonclustered (BNK_ID),
   constraint AK_BNK_NAME_BANKS unique (BNK_NAME)
)
go

/*==============================================================*/
/* Table: CITIES                                                */
/*==============================================================*/
create table CITIES (
   CTY_ID               numeric(11)          identity,
   CNT_ID               numeric(11)          not null,
   CTY_NAME             nvarchar(100)         not null,
   CTY_DESC             nvarchar(500)         null,
   constraint PK_CITIES primary key nonclustered (CTY_ID),
   constraint AK_CTY_NAME_CITIES unique (CTY_NAME)
)
go

/*==============================================================*/
/* Index: COUNTRIES_CITIES_FK                                   */
/*==============================================================*/
create index COUNTRIES_CITIES_FK on CITIES (
CNT_ID ASC
)
go

/*==============================================================*/
/* Table: COUNTRIES                                             */
/*==============================================================*/
create table COUNTRIES (
   CNT_ID               numeric(11)          identity,
   CNT_NAME             nvarchar(100)         not null,
   CNT_DESC             nvarchar(500)         null,
   constraint PK_COUNTRIES primary key nonclustered (CNT_ID),
   constraint AK_CNT_NAME_COUNTRIE unique (CNT_NAME)
)
go

/*==============================================================*/
/* Table: DEPARTMENTS                                           */
/*==============================================================*/
create table DEPARTMENTS (
   DEP_ID               numeric(11)          identity,
   DEP_NAME             nvarchar(100)         not null,
   DEP_DESC             nvarchar(500)         null,
   constraint PK_DEPARTMENTS primary key nonclustered (DEP_ID),
   constraint AK_DEP_NAME_DEPARTME unique (DEP_NAME)
)
go

/*==============================================================*/
/* Table: DEPARTMENTS_TICKETCATEGORIES                          */
/*==============================================================*/
create table DEPARTMENTS_TICKETCATEGORIES (
   DTC_ID               numeric(11)          identity,
   DEP_ID               numeric(11)          not null,
   TKC_ID               numeric(11)          not null,
   constraint PK_DEPARTMENTS_TICKETCATEGORIE primary key nonclustered (DTC_ID)
)
go

/*==============================================================*/
/* Index: TICKETCATEGORIES_DEPARTMENTS_FK                       */
/*==============================================================*/
create index TICKETCATEGORIES_DEPARTMENTS_FK on DEPARTMENTS_TICKETCATEGORIES (
TKC_ID ASC
)
go

/*==============================================================*/
/* Index: DEPARTMENTS_TICKETCATEGORIES_FK                       */
/*==============================================================*/
create index DEPARTMENTS_TICKETCATEGORIES_FK on DEPARTMENTS_TICKETCATEGORIES (
DEP_ID ASC
)
go

/*==============================================================*/
/* Table: DOWNLOADS                                             */
/*==============================================================*/
create table DOWNLOADS (
   DWN_ID               numeric(11)          identity,
   PGL_ID               numeric(11)          not null,
   DWN_NAME             nvarchar(100)         not null,
   DWN_DESC             nvarchar(500)         null,
   DWN_LINK             nvarchar(200)         not null,
   constraint PK_DOWNLOADS primary key nonclustered (DWN_ID),
   constraint AK_DWN_NAME_DOWNLOAD unique (DWN_NAME)
)
go

/*==============================================================*/
/* Index: PROGRAMMINGLANGAUGES_DOWNLOADS_FK                     */
/*==============================================================*/
create index PROGRAMMINGLANGAUGES_DOWNLOADS_FK on DOWNLOADS (
PGL_ID ASC
)
go

/*==============================================================*/
/* Table: ORDERS                                                */
/*==============================================================*/
create table ORDERS (
   ORD_ID               numeric(11)          identity,
   OST_ID               numeric(11)          not null,
   STF_ID               numeric(11)          not null,
   ACC_ID               numeric(11)          not null,
   ORD_DATE             datetime             not null,
   ORD_DESC             nvarchar(1000)        null,
   constraint PK_ORDERS primary key nonclustered (ORD_ID)
)
go

/*==============================================================*/
/* Index: ORDERSTATUSES_ORDERS_FK                               */
/*==============================================================*/
create index ORDERSTATUSES_ORDERS_FK on ORDERS (
OST_ID ASC
)
go

/*==============================================================*/
/* Index: ACCOUNTS_ORDERS_FK                                    */
/*==============================================================*/
create index ACCOUNTS_ORDERS_FK on ORDERS (
ACC_ID ASC
)
go

/*==============================================================*/
/* Index: STAFFS_ORDERS_FK                                      */
/*==============================================================*/
create index STAFFS_ORDERS_FK on ORDERS (
STF_ID ASC
)
go

/*==============================================================*/
/* Table: ORDER_LINES                                           */
/*==============================================================*/
create table ORDER_LINES (
   OLN_ID               numeric(11)          identity,
   ORD_ID               numeric(11)          not null,
   SVC_ID               numeric(11)          not null,
   OLN_QUANTITY         smallint             not null,
   OLN_PRICE            money                not null,
   OLN_DESC             nvarchar(500)         null,
   constraint PK_ORDER_LINES primary key nonclustered (OLN_ID)
)
go

/*==============================================================*/
/* Index: ORDER_ORDERLINES_FK                                   */
/*==============================================================*/
create index ORDER_ORDERLINES_FK on ORDER_LINES (
ORD_ID ASC
)
go

/*==============================================================*/
/* Index: SERVICES_ORDERLINES_FK                                */
/*==============================================================*/
create index SERVICES_ORDERLINES_FK on ORDER_LINES (
SVC_ID ASC
)
go

/*==============================================================*/
/* Table: ORDER_STATUSES                                        */
/*==============================================================*/
create table ORDER_STATUSES (
   OST_ID               numeric(11)          identity,
   OST_NAME             nvarchar(100)         not null,
   OST_DESC             nvarchar(500)         null,
   constraint PK_ORDER_STATUSES primary key nonclustered (OST_ID)
)
go

/*==============================================================*/
/* Table: PROGRAMMING_LANGUAGES                                 */
/*==============================================================*/
create table PROGRAMMING_LANGUAGES (
   PGL_ID               numeric(11)          identity,
   PGL_NAME             nvarchar(50)          not null,
   PGL_DESC             nvarchar(500)         null,
   constraint PK_PROGRAMMING_LANGUAGES primary key nonclustered (PGL_ID),
   constraint AK_PGL_NAME_PROGRAMM unique (PGL_NAME)
)
go

/*==============================================================*/
/* Table: SERVICES                                              */
/*==============================================================*/
create table SERVICES (
   SVC_ID               numeric(11)          identity,
   SVT_ID               numeric(11)          not null,
   SVC_NAME             nvarchar(100)         not null,
   SVC_PRICE            money                not null,
   SVC_DESC             nvarchar(1000)        null,
   constraint PK_SERVICES primary key nonclustered (SVC_ID),
   constraint AK_SVC_NAME_SERVICES unique (SVC_NAME)
)
go

/*==============================================================*/
/* Index: SERVICETYPES_SERVICES_FK                              */
/*==============================================================*/
create index SERVICETYPES_SERVICES_FK on SERVICES (
SVT_ID ASC
)
go

/*==============================================================*/
/* Table: SERVICETYPES_TICKETCATEGORIES                         */
/*==============================================================*/
create table SERVICETYPES_TICKETCATEGORIES (
   STC_ID               numeric(11)          identity,
   SVT_ID               numeric(11)          not null,
   TKC_ID               numeric(11)          not null,
   constraint PK_SERVICETYPES_TICKETCATEGORI primary key nonclustered (STC_ID)
)
go

/*==============================================================*/
/* Index: SERVICETYPES_TICKETCATEGORIES_FK                      */
/*==============================================================*/
create index SERVICETYPES_TICKETCATEGORIES_FK on SERVICETYPES_TICKETCATEGORIES (
SVT_ID ASC
)
go

/*==============================================================*/
/* Index: TICKETCATEGORIES_SERVICETYPES_FK                      */
/*==============================================================*/
create index TICKETCATEGORIES_SERVICETYPES_FK on SERVICETYPES_TICKETCATEGORIES (
TKC_ID ASC
)
go

/*==============================================================*/
/* Table: SERVICE_TYPES                                         */
/*==============================================================*/
create table SERVICE_TYPES (
   SVT_ID               numeric(11)          identity,
   SVT_NAME             nvarchar(100)         not null,
   SVT_DESC             nvarchar(500)         null,
   constraint PK_SERVICE_TYPES primary key nonclustered (SVT_ID),
   constraint AK_SVT_NAME_SERVICE_ unique (SVT_NAME)
)
go

/*==============================================================*/
/* Table: STAFFS                                                */
/*==============================================================*/
create table STAFFS (
   STF_ID               numeric(11)          identity,
   USR_ID               numeric(11)          not null,
   STF_CODE             char(9)              not null,
   STF_FULL_NAME        nvarchar(50)          not null,
   STF_GENDER           bit                  not null,
   STF_BIRTHDAY         datetime             null,
   STF_HIRE_DATE        datetime             not null,
   STF_PHONE            nvarchar(15)          not null,
   STF_ADDRESS          nvarchar(200)         null,
   STF_DESC             nvarchar(500)         null,
   constraint PK_STAFFS primary key nonclustered (STF_ID),
   constraint AK_STF_CODE_STAFFS unique (STF_CODE)
)
go

/*==============================================================*/
/* Index: USERS_STAFFS_FK                                       */
/*==============================================================*/
create index USERS_STAFFS_FK on STAFFS (
USR_ID ASC
)
go

/*==============================================================*/
/* Table: TICKETS                                               */
/*==============================================================*/
create table TICKETS (
   TKT_ID               numeric(11)          identity,
   SVT_ID               numeric(11)          not null,
   DEP_ID               numeric(11)          not null,
   OLN_ID               numeric(11)          not null,
   TKC_ID               numeric(11)          not null,
   STF_ID               numeric(11)          null,
   TKS_ID               numeric(11)          not null,
   TKT_NUMBER           char(9)              null,
   TKT_SUBJECT          nvarchar(100)         not null,
   TKT_SERVICE_PASSWORD nvarchar(100)         null,
   TKT_CONTENT          nvarchar(1000)        not null,
   TKT_TIMESTAMP        timestamp            not null,
   TKT_ATTACHMENT       nvarchar(100)         null,
   PARENT_ID            int                  not null,
   PARENT_TYPE          nvarchar(50)          not null,
   constraint PK_TICKETS primary key nonclustered (TKT_ID),
   constraint AK_TKT_NUMBER_TICKETS unique (TKT_NUMBER)
)
go

/*==============================================================*/
/* Index: TICKETCATEGORIES_TICKETS_FK                           */
/*==============================================================*/
create index TICKETCATEGORIES_TICKETS_FK on TICKETS (
TKC_ID ASC
)
go

/*==============================================================*/
/* Index: DEPARTMENTS_TICKETS_FK                                */
/*==============================================================*/
create index DEPARTMENTS_TICKETS_FK on TICKETS (
DEP_ID ASC
)
go

/*==============================================================*/
/* Index: SERVICETYPES_TICKETS_FK                               */
/*==============================================================*/
create index SERVICETYPES_TICKETS_FK on TICKETS (
SVT_ID ASC
)
go

/*==============================================================*/
/* Index: STAFFS_TICKETS_FK                                     */
/*==============================================================*/
create index STAFFS_TICKETS_FK on TICKETS (
STF_ID ASC
)
go

/*==============================================================*/
/* Index: TICKETSTATUSES_TICKETS_FK                             */
/*==============================================================*/
create index TICKETSTATUSES_TICKETS_FK on TICKETS (
TKS_ID ASC
)
go

/*==============================================================*/
/* Index: ORDERLINES_TICKETS_FK                                 */
/*==============================================================*/
create index ORDERLINES_TICKETS_FK on TICKETS (
OLN_ID ASC
)
go

/*==============================================================*/
/* Table: TICKET_CATEGORIES                                     */
/*==============================================================*/
create table TICKET_CATEGORIES (
   TKC_ID               numeric(11)          identity,
   TKC_NAME             nvarchar(100)         not null,
   TKC_DESC             nvarchar(1000)        null,
   constraint PK_TICKET_CATEGORIES primary key nonclustered (TKC_ID),
   constraint AK_TKC_NAME_TICKET_C unique (TKC_NAME)
)
go

/*==============================================================*/
/* Table: TICKET_COMMENTS                                       */
/*==============================================================*/
create table TICKET_COMMENTS (
   TCM_ID               numeric(11)          identity,
   TKT_ID               numeric(11)          not null,
   TCM_CONTENT          nvarchar(1000)        not null,
   TCM_TIMESTAMP        timestamp            not null,
   PARENT_ID            int                  not null,
   PARENT_TYPE          nvarchar(50)          not null,
   constraint PK_TICKET_COMMENTS primary key nonclustered (TCM_ID)
)
go

/*==============================================================*/
/* Index: TICKETS_TICKETCOMMENTS_FK                             */
/*==============================================================*/
create index TICKETS_TICKETCOMMENTS_FK on TICKET_COMMENTS (
TKT_ID ASC
)
go

/*==============================================================*/
/* Table: TICKET_STATUSES                                       */
/*==============================================================*/
create table TICKET_STATUSES (
   TKS_ID               numeric(11)          identity,
   TKS_NAME             nvarchar(100)         not null,
   TKS_DESC             nvarchar(500)         null,
   constraint PK_TICKET_STATUSES primary key nonclustered (TKS_ID),
   constraint AK_TKS_NAME_TICKET_S unique (TKS_NAME)
)
go

/*==============================================================*/
/* Table: USERS                                                 */
/*==============================================================*/
create table USERS (
   USR_ID               numeric(11)          identity,
   UST_ID               numeric(11)          not null,
   USR_USERNAME         nvarchar(50)          not null,
   USR_PASSWORD         nvarchar(32)          not null,
   USR_EMAIL            nvarchar(100)         not null,
   USR_IS_ACTIVE        bit                  not null,
   constraint PK_USERS primary key nonclustered (USR_ID),
   constraint AK_USR_USERNAME_USERS unique (USR_USERNAME),
   constraint AK_USR_EMAIL_USERS unique (USR_EMAIL)
)
go

/*==============================================================*/
/* Index: USERTYPES_USERS_FK                                    */
/*==============================================================*/
create index USERTYPES_USERS_FK on USERS (
UST_ID ASC
)
go

/*==============================================================*/
/* Table: USER_TYPES                                            */
/*==============================================================*/
create table USER_TYPES (
   UST_ID               numeric(11)          identity,
   UST_NAME             nvarchar(100)         not null,
   UST_DESC             nvarchar(500)         null,
   constraint PK_USER_TYPES primary key nonclustered (UST_ID),
   constraint AK_UST_NAME_USER_TYP unique (UST_NAME)
)
go

alter table ACCOUNTS
   add constraint FK_ACCOUNTS_ACCOUNTTY_ACCOUNT_ foreign key (ACT_ID)
      references ACCOUNT_TYPES (ACT_ID)
go

alter table ACCOUNTS
   add constraint FK_ACCOUNTS_BANKS_ACC_BANKS foreign key (BNK_ID)
      references BANKS (BNK_ID)
go

alter table ACCOUNTS
   add constraint FK_ACCOUNTS_CITIES_AC_CITIES foreign key (CTY_ID)
      references CITIES (CTY_ID)
go

alter table ACCOUNTS
   add constraint FK_ACCOUNTS_COUNTRIES_COUNTRIE foreign key (CNT_ID)
      references COUNTRIES (CNT_ID)
go

alter table ACCOUNTS
   add constraint FK_ACCOUNTS_RELATIONS_USERS foreign key (USR_ID)
      references USERS (USR_ID)
go

alter table CITIES
   add constraint FK_CITIES_COUNTRIES_COUNTRIE foreign key (CNT_ID)
      references COUNTRIES (CNT_ID)
go

alter table DEPARTMENTS_TICKETCATEGORIES
   add constraint FK_DEPARTME_DEPARTMEN_DEPARTME foreign key (DEP_ID)
      references DEPARTMENTS (DEP_ID)
go

alter table DEPARTMENTS_TICKETCATEGORIES
   add constraint FK_DEPARTME_TICKETCAT_TICKET_C foreign key (TKC_ID)
      references TICKET_CATEGORIES (TKC_ID)
go

alter table DOWNLOADS
   add constraint FK_DOWNLOAD_PROGRAMMI_PROGRAMM foreign key (PGL_ID)
      references PROGRAMMING_LANGUAGES (PGL_ID)
go

alter table ORDERS
   add constraint FK_ORDERS_ACCOUNTS__ACCOUNTS foreign key (ACC_ID)
      references ACCOUNTS (ACC_ID)
go

alter table ORDERS
   add constraint FK_ORDERS_ORDERSTAT_ORDER_ST foreign key (OST_ID)
      references ORDER_STATUSES (OST_ID)
go

alter table ORDERS
   add constraint FK_ORDERS_STAFFS_OR_STAFFS foreign key (STF_ID)
      references STAFFS (STF_ID)
go

alter table ORDER_LINES
   add constraint FK_ORDER_LI_ORDER_ORD_ORDERS foreign key (ORD_ID)
      references ORDERS (ORD_ID)
go

alter table ORDER_LINES
   add constraint FK_ORDER_LI_SERVICES__SERVICES foreign key (SVC_ID)
      references SERVICES (SVC_ID)
go

alter table SERVICES
   add constraint FK_SERVICES_SERVICETY_SERVICE_ foreign key (SVT_ID)
      references SERVICE_TYPES (SVT_ID)
go

alter table SERVICETYPES_TICKETCATEGORIES
   add constraint FK_SERVICET_SERVICETY_SERVICE_ foreign key (SVT_ID)
      references SERVICE_TYPES (SVT_ID)
go

alter table SERVICETYPES_TICKETCATEGORIES
   add constraint FK_SERVICET_TICKETCAT_TICKET_C foreign key (TKC_ID)
      references TICKET_CATEGORIES (TKC_ID)
go

alter table STAFFS
   add constraint FK_STAFFS_USERS_STA_USERS foreign key (USR_ID)
      references USERS (USR_ID)
go

alter table TICKETS
   add constraint FK_TICKETS_DEPARTMEN_DEPARTME foreign key (DEP_ID)
      references DEPARTMENTS (DEP_ID)
go

alter table TICKETS
   add constraint FK_TICKETS_ORDERLINE_ORDER_LI foreign key (OLN_ID)
      references ORDER_LINES (OLN_ID)
go

alter table TICKETS
   add constraint FK_TICKETS_SERVICETY_SERVICE_ foreign key (SVT_ID)
      references SERVICE_TYPES (SVT_ID)
go

alter table TICKETS with NOCHECK
   add constraint FK_TICKETS_STAFFS_TI_STAFFS foreign key (STF_ID)
      references STAFFS (STF_ID)
go

alter table TICKETS
   add constraint FK_TICKETS_TICKETCAT_TICKET_C foreign key (TKC_ID)
      references TICKET_CATEGORIES (TKC_ID)
go

alter table TICKETS
   add constraint FK_TICKETS_TICKETSTA_TICKET_S foreign key (TKS_ID)
      references TICKET_STATUSES (TKS_ID)
go

alter table TICKET_COMMENTS
   add constraint FK_TICKET_C_TICKETS_T_TICKETS foreign key (TKT_ID)
      references TICKETS (TKT_ID)
go

alter table USERS
   add constraint FK_USERS_USERTYPES_USER_TYP foreign key (UST_ID)
      references USER_TYPES (UST_ID)
go

